﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Text;

/// <summary>
///KHXX 的摘要说明
/// </summary>
public class KHXX
{
  public KHXX()
  {
    //
    //TODO: 在此处添加构造函数逻辑
    //
  }

  public static SqlDataReader GetXX(string sKHID)
  {
    string sSql = "select * from jcxx_khxx where khid=@0";
    SqlParameter[] cmdParams = { new SqlParameter("@0", SqlDbType.VarChar, 20) };
    cmdParams[0].Value = sKHID;
    SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql, cmdParams);
    return dr;
  }

  public static string GetKHMC(string sKHID)
  {
    string sKHMC = "";
    string sSql = "select * from jcxx_khxx where khid=@0";
    SqlParameter[] cmdParams = { new SqlParameter("@0", SqlDbType.VarChar, 20) };
    cmdParams[0].Value = sKHID;
    SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql, cmdParams);
    if (dr.Read())
    {
      sKHMC = dr["khmc"].ToString();
    }
    dr.Close();
    return sKHMC;
  }

  public static DataSet GetYQXZ(string sKHID)
  {
    string sSql = @"SELECT * from jcxx_khyqxz where khid=@0";
    SqlParameter[] cmdParams = { new SqlParameter("@0", SqlDbType.VarChar, 20) };
    cmdParams[0].Value = sKHID;
    DataSet dsYQXZ = DbHelperSQL.Query(sSql, cmdParams);
    return dsYQXZ;
  }

  public static DataSet GetYQXZ_ALL(string sKHID)
  {
    string sSql = @"SELECT a.*,b.yqxz 
from jcxx_khyqxz a,jcxx_yqxz b,
(select top 1 * from SJLR_JGTZSJ order by ID desc) d
where a.khid=@0 and a.yqxzid=b.yqxzid and charindex(b.YQXZ,d.YQXZ)>0";
    SqlParameter[] cmdParams = { new SqlParameter("@0", SqlDbType.VarChar, 20) };
    cmdParams[0].Value = sKHID;
    DataSet dsYQXZ = DbHelperSQL.Query(sSql, cmdParams);
    return dsYQXZ;
  }

  public static string GetYE(string sKHID)
  {
    string s = "0";
    string sSql = "select * from jcxx_khxx where khid=@0";
    SqlParameter[] cmdParams = { new SqlParameter("@0", SqlDbType.VarChar, 20) };
    cmdParams[0].Value = sKHID;
    SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql, cmdParams);
    if (dr.Read())
    {
      s = string.IsNullOrEmpty(dr["zhye"].ToString()) ? "0" : dr["zhye"].ToString();
    }
    dr.Close();
    return s;
  }

  public static string GetRQ(string sKHID)
  {
    string s = DateTime.Now.ToString();
    string sSql = "select * from jcxx_khxx where khid=@0";
    SqlParameter[] cmdParams = { new SqlParameter("@0", SqlDbType.VarChar, 20) };
    cmdParams[0].Value = sKHID;
    SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql, cmdParams);
    if (dr.Read())
    {
      s = string.IsNullOrEmpty(dr["rq"].ToString()) ? DateTime.Now.ToString() : dr["rq"].ToString();
    }
    dr.Close();
    return s;
  }

  public static string GetXSKH()
  {
    string s = "0";
    string sSql = "select count(khid) s from jcxx_khxx";
    SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql);
    if (dr.Read())
    {
      s = dr["s"].ToString();
    }
    dr.Close();
    return s;
  }

  public static DataSet GetDS()
  {
    string sSql = "select * from jcxx_khxx";
    DataSet ds = DbHelperSQL.Query(sSql);
    return ds;
  }

  public static DataSet GetDS_XX(string ic)
  {
    StringBuilder sbSql = new StringBuilder();
    //    sbSql.Append("SELECT  ROW_NUMBER() OVER (ORDER BY khid desc) AS xh,* FROM(");
    //    sbSql.Append(" select a.*,b.KHJB,c.DWMC from jcxx_khxx a,jcxx_khjb b,jcxx_dw c where a.khjbid=b.khjbid and a.dwid=c.dwid ");
    //    if (!string.IsNullOrEmpty(ic))
    //    {
    //      sbSql.Append("and ic='" + ic + "' ");
    //    }
    //    sbSql.Append(@") A OUTER APPLY(SELECT YQXZ=STUFF(
    //      REPLACE(
    //       REPLACE(
    //               (select yqxz from 
    //                 (SELECT yqxz,khid FROM jcxx_yqxz b,jcxx_khyqxz N WHERE b.yqxzid=n.yqxzid ) b
    //                WHERE b.khid = A.khid FOR XML AUTO), ");
    //sbSql.Append("'<b yqxz=\"', ','),'\"/>', ''), 1, 1, '')) N ");
    sbSql.Append("SELECT ROW_NUMBER() OVER (ORDER BY a.khid) AS XH,* FROM v_khxx_khjb_dw a OUTER APPLY f_yqxz_hb(A.KHID)");
    if (!string.IsNullOrEmpty(ic))
    {
      sbSql.Append("where ic='" + ic + "' ");
    }

    DataSet ds = DbHelperSQL.Query(sbSql.ToString());
    return ds;
  }

  public static SqlDataReader GetXTYH(string year, string Month, string lb)
  {
    StringBuilder sbSql = new StringBuilder();
    sbSql.Append("select khjb,ic,count(khid) a from jcxx_khxx a,jcxx_khjb b where a.khjbid=b.khjbid ");
    if (!string.IsNullOrEmpty(lb))
    {
      sbSql.Append("and convert(varchar(6),rq,112)='" + year + Month + "' ");
    }
    else
    {
      sbSql.Append("and convert(varchar(6),rq,112)<='" + year + Month + "' ");
    }
    sbSql.Append("group by khjb,ic");
    SqlDataReader dr = DbHelperSQL.ExecuteReader(sbSql.ToString());
    return dr;
  }

  public static bool ExistMC(string mc, string bs)
  {
    bool flag = false;
    string sSql = "select * from jcxx_khxx where khmc=@0 and kh=@1";
    SqlParameter[] cmdParams = { new SqlParameter("@0", SqlDbType.VarChar, 20), new SqlParameter("@1", SqlDbType.VarChar, 20) };
    cmdParams[0].Value = mc;
    cmdParams[1].Value = bs;
    SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql, cmdParams);
    if (dr.Read())
    {
      flag = true;
    }
    dr.Close();
    return flag;
  }

  public static DataSet GetDS_JGTZKH()
  {
    StringBuilder sbSql = new StringBuilder();
    sbSql.Append(@"select a.* from 
   (select a.*,b.BYBS,b.cbsjid,CONVERT(varchar(10),cbsj,120) cbsj from 
	 (SELECT ROW_NUMBER() OVER (ORDER BY a.khid) AS XH,* 
	 FROM v_khxx_khjb_dw a OUTER APPLY f_yqxz_hb(A.KHID)
	 ) a,
     SJLR_CBSJ b,
	 (select top 1 * from SJLR_JGTZSJ order by ID desc) c,
	 (select distinct a.KHID 
	  from JCXX_KHXX a,JCXX_KHYQXZ b,JCXX_YQXZ c,
	     (select top 1 * from SJLR_JGTZSJ order by ID desc) d
	  where a.KHID=b.KHID and b.YQXZID=c.YQXZID and charindex(c.YQXZ,d.YQXZ)>0) d
   where b.dlm=@0 and a.KHID=b.KHID and b.CBSJ >=c.QSRQ and b.CBSJ<=c.JSRQ
     and a.KHID=d.KHID) a
   left join 
   (select a.* from SJLR_jgtz_CBSJ a,(select top 1 * from SJLR_JGTZSJ order by ID desc) b where a.CBSJ>b.JSRQ) b
   on a.KHID=b.KHID and a.BYBS=b.BYBS 
 where b.KHID is null");
    SqlParameter[] cmdParams = { new SqlParameter("@0", SqlDbType.VarChar, 20) };
    cmdParams[0].Value = SysManage.Get_User;
    DataSet ds = DbHelperSQL.Query(sbSql.ToString(), cmdParams);
    return ds;
  }

  public static DataSet GetDS_JGTZKH(string zt)
  {
    StringBuilder sbSql = new StringBuilder();
    sbSql.Append(@"select a.* from 
   (select a.*,b.BYBS,b.cbsjid,CONVERT(varchar(10),cbsj,120) cbsj from 
	 (SELECT ROW_NUMBER() OVER (ORDER BY a.khid) AS XH,* 
	 FROM v_khxx_khjb_dw a OUTER APPLY f_yqxz_hb(A.KHID)
	 ) a,
     SJLR_CBSJ b,
	 (select top 1 * from SJLR_JGTZSJ order by ID desc) c,
	 (select distinct a.KHID 
	  from JCXX_KHXX a,JCXX_KHYQXZ b,JCXX_YQXZ c,
	     (select top 1 * from SJLR_JGTZSJ order by ID desc) d
	  where a.KHID=b.KHID and b.YQXZID=c.YQXZID and charindex(c.YQXZ,d.YQXZ)>0) d
   where a.KHID=b.KHID and b.CBSJ >=c.QSRQ and b.CBSJ<=c.JSRQ
     and a.KHID=d.KHID) a
   left join 
   (select a.* from SJLR_CBSJ a,(select top 1 * from SJLR_JGTZSJ order by ID desc) b where a.CBSJ>b.JSRQ) b
   on a.KHID=b.KHID and a.BYBS=b.BYBS 
");
    if (zt == "1")
      sbSql.Append("where b.khid is null");
    else
      sbSql.Append("where b.khid is not null");
    DataSet ds = DbHelperSQL.Query(sbSql.ToString());
    return ds;
  }
}
